<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;

class Tifybyhxjlcx extends Common
{

	//列表--非常规和进场
    public function index()
    {

        $where = [];
        $where['t3.hx_status'] = 2;
        $start = $this->request->get('start');
        $this->assign('start',$start);
        $end = $this->request->get('end');
        $this->assign('end',$end);
        $hxmdbh = $this->request->get('hxmdbh');
        $this->assign('hxmdbh',$hxmdbh);
        $ysqbh = $this->request->get('ysqbh');
        $this->assign('ysqbh',$ysqbh);
        $jxsmc = $this->request->get('jxsmc');
        $this->assign('jxsmc',$jxsmc);
        $wdmc = $this->request->get('wdmc');
        $this->assign('wdmc',$wdmc);
        $pp = $this->request->get('pp');
        $this->assign('pp',$pp);
        $sjfykm = $this->request->get('sjfykm');
        $this->assign('sjfykm',$sjfykm);
        $sqr = $this->request->get('sqr');
        $this->assign('sqr',$sqr);

        if($start&&$end){
            $where['t.ctime'] = ['between',[strtotime($start),strtotime($end)]];
        }elseif($start){
            $where['t.ctime'] = ['>',strtotime($start)];
        }elseif($end){
            $where['t.ctime'] = ['<',strtotime($end)];
        }
        if($hxmdbh){
            $where['t.sn'] = ['like',"%{$hxmdbh}%"];
        }
        if($ysqbh){
            $where['t4.sn'] = ['like',"%{$ysqbh}%"];
        }
        if($jxsmc){
            $jxsids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['t.jxs_id'] = ['in',$jxsids];
        }
        if($wdmc){
            $wdids = db('cg_net')->where('title','like',"%{$wdmc}%")->column('id');
            $where['t3.net_id'] = ['in',$wdids];
        }
        if($pp){
//            echo db('pro_brands')->getLastSql();
            $where['t3.brand'] = [['exp','is null'],['in',$pp],'or'];
            $plids = db('pro_category')->where('brand_id',$pp)->column('id');
            $where['t3.category'] = [['exp','is null'],['in',$plids],'or'];
            $xlids = db('pro_series')->where('category_id','in',$plids)->column('id');
            $where['t3.series'] = [['exp','is null'],['in',$xlids],'or'];
            $skuids = db('pro_sku')->where('series_id','in',$xlids)->column('id');
            $where['t3.sku'] = [['exp','is null'],['in',$skuids],'or'];
            $pro_ids = db('pro_products')->where('sku_id','in',$skuids)->column('id');
            $where['t3.product'] = [['exp','is null'],['in',$pro_ids],'or'];
            $where['t3.tm_id'] = [['exp','is null'],['in',$pro_ids],'or'];
        }
        if($sjfykm){
            $where['feetype'] = $sjfykm;
        }
        if($sqr){
            $sqrids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['t.cuser'] = ['in',$sqrids];
        }

        $count = Db::table('cg_ti_hexiao t')
            ->join('cg_ti_hexiao_list t2','t2.hx_id=t.id')
            ->join('cg_ti_list t3','t3.id=t2.list_id')
            ->join('cg_ti t4','t4.id=t3.ti_id')
            ->where($where)
            ->count();
        $data = Db::table('cg_ti_hexiao t')
            ->join('cg_ti_hexiao_list t2','t2.hx_id=t.id')
            ->join('cg_ti_list t3','t3.id=t2.list_id')
            ->join('cg_ti t4','t4.id=t3.ti_id')
            ->order("t.id desc")
            ->where($where)
            ->field('t.sn hxmdbh,t.id,t4.sn ydbh,t3.sn ymxbh,t4.id ydid,t4.cate_id,t3.id ymxid,t4.type_id,t.jxs_id,t3.net_id,t3.brand,t3.category,t3.series,t3.sku,t3.product,t3.tm_id,t3.feetype,t3.pizhunhanshui,t3.hx_yihexiao2,t3.jrbm,t.cuser,t.ctime,t2.beizhu')
            ->paginate(10,$count);
//        echo Db::table('cg_ti')->getLastSql();
        $page = $data->render();
        $data = $data->items();
        $heji_yhx=0;
        $heji_whx=0;
        foreach($data as $k=>$v){
            $v['type_title'] = db('cg_ti_type')->where('id',$v['type_id'])->value('title');
            $v['cate_title'] = db('cg_ti_cate')->where('id',$v['cate_id'])->value('title');
            $v['jxs_title'] = db('cg_jxs')->where('id',$v['jxs_id'])->value('title');
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_title'] = db('cg_system t')->join('cg_net t2','t2.system_id=t.id')->where('t2.id',$v['net_id'])->value('t.title');
            if($v['brand']){
                $v['brand'] = db('pro_brands')->where('id',$v['brand'])->value('title');
            }elseif($v['category']){
                $v['brand'] = db('pro_brands t')->join('pro_category t2','t2.brand_id=t.id')->where('t2.id',$v['category'])->value('t.brand_name');
            }elseif($v['series']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->where('t3.id',$v['series'])->value('t.brand_name');
            }elseif($v['sku']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->where('t4.id',$v['sku'])->value('t.brand_name');
            }elseif($v['product']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->join('pro_products t5','t5.id=t4.id')
                    ->where('t5.id',$v['product'])->value('t.brand_name');
            }elseif($v['tm_id']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->join('pro_products t5','t5.id=t4.id')
                    ->where('t5.id',$v['tm_id'])->value('t.brand_name');
            }
            $v['sjfykm'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            $v['whxje'] = $v['pizhunhanshui']-$v['hx_yihexiao2'];
            $heji_yhx += $v['hx_yihexiao2'];
            $heji_whx += $v['whxje'];
            $v['jrbm'] = db('department')->where('id',$v['jrbm'])->value('de_name');
            $v['cuser'] = db('employee')->where('id',$v['cuser'])->value('em_name');
            $v['ctime'] = date('Y-m-d',$v['ctime']);
            //查询操作记录
            $czjl = db('cg_ti_list_log')->where('list_id',$v['ymxid'])->order('ctime desc')->select();
            $v['czjl'] = isset($czjl[0]['brief'])?$czjl[0]['brief']:'';
            foreach($czjl as $k1=>$v1){
                $v1['cuser'] = db('employee')->where('id',$v1['cuser'])->value('em_name');
                $v1['ctime'] = date('Y-m-d H:i',$v1['ctime']);
                $czjl[$k1] = $v1;
            }
            $v['czjlxq'] = $czjl;
            $data[$k] = $v;
        }

        //已核销金额合计和未核销金额合计
        $this->assign('heji_yhx',$heji_yhx);
        $this->assign('heji_whx',$heji_whx);
        $this->assign('data',$data);
        $this->assign('page',$page);

        //查询品牌
        $brands = db('pro_brands')->field('id,brand_name')->select();
        $this->assign('brands',$brands);
        $fykm3 = db('cg_feiyongkemu3')->field('id,title')->select();
        $this->assign('fykm3',$fykm3);

        return $this->fetch();
    }

    //导出excel--非常规和进场
    public function excel(){
        //获取数据
        $where = [];
        $where['t3.hx_status'] = 2;
        $start = $this->request->get('start');
        $end = $this->request->get('end');
        $hxmdbh = $this->request->get('hxmdbh');
        $ysqbh = $this->request->get('ysqbh');
        $jxsmc = $this->request->get('jxsmc');
        $wdmc = $this->request->get('wdmc');
        $pp = $this->request->get('pp');
        $sjfykm = $this->request->get('sjfykm');
        $sqr = $this->request->get('sqr');

        if($start&&$end){
            $where['t.ctime'] = ['between',[strtotime($start),strtotime($end)]];
        }elseif($start){
            $where['t.ctime'] = ['>',strtotime($start)];
        }elseif($end){
            $where['t.ctime'] = ['<',strtotime($end)];
        }
        if($hxmdbh){
            $where['t.sn'] = ['like',"%{$hxmdbh}%"];
        }
        if($ysqbh){
            $where['t4.sn'] = ['like',"%{$ysqbh}%"];
        }
        if($jxsmc){
            $jxsids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['t.jxs_id'] = ['in',$jxsids];
        }
        if($wdmc){
            $wdids = db('cg_net')->where('title','like',"%{$wdmc}%")->column('id');
            $where['t3.net_id'] = ['in',$wdids];
        }
        if($pp){
//            echo db('pro_brands')->getLastSql();
            $where['t3.brand'] = [['exp','is null'],['in',$pp],'or'];
            $plids = db('pro_category')->where('brand_id',$pp)->column('id');
            $where['t3.category'] = [['exp','is null'],['in',$plids],'or'];
            $xlids = db('pro_series')->where('category_id','in',$plids)->column('id');
            $where['t3.series'] = [['exp','is null'],['in',$xlids],'or'];
            $skuids = db('pro_sku')->where('series_id','in',$xlids)->column('id');
            $where['t3.sku'] = [['exp','is null'],['in',$skuids],'or'];
            $pro_ids = db('pro_products')->where('sku_id','in',$skuids)->column('id');
            $where['t3.product'] = [['exp','is null'],['in',$pro_ids],'or'];
            $where['t3.tm_id'] = [['exp','is null'],['in',$pro_ids],'or'];
        }
        if($sjfykm){
            $where['feetype'] = $sjfykm;
        }
        if($sqr){
            $sqrids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['t.cuser'] = ['in',$sqrids];
        }

        $data = Db::table('cg_ti_hexiao t')
            ->join('cg_ti_hexiao_list t2','t2.hx_id=t.id')
            ->join('cg_ti_list t3','t3.id=t2.list_id')
            ->join('cg_ti t4','t4.id=t3.ti_id')
            ->order("t.id desc")
            ->where($where)
            ->field('t.sn hxmdbh,t.id,t4.sn ydbh,t3.sn ymxbh,t4.id ydid,t4.cate_id,t3.id ymxid,t4.type_id,t.jxs_id,t3.net_id,t3.brand,t3.category,t3.series,t3.sku,t3.product,t3.tm_id,t3.feetype,t3.pizhunhanshui,t3.hx_yihexiao2,t3.jrbm,t.cuser,t.ctime,t2.beizhu')
            ->select();
//        echo Db::table('cg_ti')->getLastSql();
        $heji_yhx=0;
        $heji_whx=0;
        foreach($data as $k=>$v){
            $v['type_title'] = db('cg_ti_type')->where('id',$v['type_id'])->value('title');
            $v['cate_title'] = db('cg_ti_cate')->where('id',$v['cate_id'])->value('title');
            $v['jxs_title'] = db('cg_jxs')->where('id',$v['jxs_id'])->value('title');
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_title'] = db('cg_system t')->join('cg_net t2','t2.system_id=t.id')->where('t2.id',$v['net_id'])->value('t.title');
            if($v['brand']){
                $v['brand'] = db('pro_brands')->where('id',$v['brand'])->value('title');
            }elseif($v['category']){
                $v['brand'] = db('pro_brands t')->join('pro_category t2','t2.brand_id=t.id')->where('t2.id',$v['category'])->value('t.brand_name');
            }elseif($v['series']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->where('t3.id',$v['series'])->value('t.brand_name');
            }elseif($v['sku']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->where('t4.id',$v['sku'])->value('t.brand_name');
            }elseif($v['product']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->join('pro_products t5','t5.id=t4.id')
                    ->where('t5.id',$v['product'])->value('t.brand_name');
            }elseif($v['tm_id']){
                $v['brand'] = db('pro_brands t')
                    ->join('pro_category t2','t2.brand_id=t.id')
                    ->join('pro_series t3','t3.category_id=t2.id')
                    ->join('pro_sku t4','t4.series_id=t3.id')
                    ->join('pro_products t5','t5.id=t4.id')
                    ->where('t5.id',$v['tm_id'])->value('t.brand_name');
            }
            $v['sjfykm'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            $v['whxje'] = $v['pizhunhanshui']-$v['hx_yihexiao2'];
            $heji_yhx += $v['hx_yihexiao2'];
            $heji_whx += $v['whxje'];
            $v['jrbm'] = db('department')->where('id',$v['jrbm'])->value('de_name');
            $v['cuser'] = db('employee')->where('id',$v['cuser'])->value('em_name');
            $v['ctime'] = date('Y-m-d',$v['ctime']);
            //查询操作记录
            $czjl = db('cg_ti_list_log')->where('list_id',$v['ymxid'])->order('ctime desc')->select();
            $v['czjl'] = isset($czjl[0]['brief'])?$czjl[0]['brief']:'';
            foreach($czjl as $k1=>$v1){
                $v1['cuser'] = db('employee')->where('id',$v1['cuser'])->value('em_name');
                $v1['ctime'] = date('Y-m-d H:i',$v1['ctime']);
                $czjl[$k1] = $v1;
            }
            $v['czjlxq'] = $czjl;
            $data[$k] = $v;
        }

        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $as =  $objPHPExcel->getActiveSheet();
        $as->setTitle("TI费用不予核销记录-非常规和进场");
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    'color' => array('argb' => ''),
                ),
            ),
        );
        $as->mergeCells('A1:P1');
        $as->setCellValue('A1','TI费用不予核销记录-非常规和进场');
        //加粗
        $as->getStyle('A1')->getFont()->setBold(true);
        //字体
        $as->getStyle('A1')->getFont()->setSize(14);
        $as->mergeCells('A2:P2');
        $username = db('employee')->where('id',session('employee_id'))->value('em_name');
        $as->setCellValue('A2','注：本表单由软件导出，导出时间：'.date('Y-m-d H:i').'导出人：'.$username);
        $as->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $as->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //标题
        $as->getStyle('A3:P3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $as->getStyle('A3:P3')->getFill()->getStartColor()->setARGB('0099CCFF');
        $as->setCellValue('A3','序号');
        $as->setCellValue('B3','核销面单编号');
        $as->setCellValue('C3','原明细编号');
        $as->setCellValue('D3','原申请类型');
        $as->setCellValue('E3','经销商名称');
        $as->setCellValue('F3','系统名称');
        $as->setCellValue('G3','网点名称');
        $as->setCellValue('H3','品牌');
        $as->setCellValue('I3','三级费用科目');
        $as->setCellValue('J3','已核销金额');
        $as->setCellValue('K3','未核销金额');
        $as->setCellValue('L3','费用计入部门');
        $as->setCellValue('M3','操作记录');
        $as->setCellValue('N3','申请人');
        $as->setCellValue('O3','核销日期');
        $as->setCellValue('P3','备注');

        foreach($data as $k=>$v){
            $n = 4+$k;
            $as->setCellValue('A'.$n,$k+1);
            $as->setCellValue('B'.$n,''.$v['hxmdbh']);
            $as->setCellValue('C'.$n,$v['ydbh'].'-'.$v['ymxbh']);
            $as->setCellValue('D'.$n,$v['type_title'].'-'.$v['cate_title']);
            $as->setCellValue('E'.$n,$v['jxs_title']);
            $as->setCellValue('F'.$n,$v['system_title']);
            $as->setCellValue('G'.$n,$v['net_title']);
            $as->setCellValue('H'.$n,$v['brand']);
            $as->setCellValue('I'.$n,$v['sjfykm']);
            $as->setCellValue('J'.$n,$v['hx_yihexiao2']);
            $as->setCellValue('K'.$n,$v['whxje']);
            $as->setCellValue('L'.$n,$v['jrbm']);
            $as->setCellValue('M'.$n,$v['czjl']);
            $as->setCellValue('N'.$n,$v['cuser']);
            $as->setCellValue('O'.$n,$v['ctime']);
            $as->setCellValue('P'.$n,$v['beizhu']);
        }
        $as->mergeCells('A'.(count($data)+4).':I'.(count($data)+4));
        $as->setCellValue('A'.(count($data)+4),'合计');
        $as->setCellValue('J'.(count($data)+4),$heji_yhx);
        $as->setCellValue('K'.(count($data)+4),$heji_whx);
        $as->getStyle('A'.(count($data)+4))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        $n = $as->getHighestRow();
        $as->getStyle('A1:P'.$n)->applyFromArray($styleArray);
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=TI费用不予核销记录-非常规和进场.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }

    //列表--常规
    public function cgindex()
    {
        $where = [];
        $where['t3.buyu_he'] = 1;
        $start = $this->request->get('start');
        $this->assign('start',$start);
        $end = $this->request->get('end');
        $this->assign('end',$end);
        $hxmdbh = $this->request->get('hxmdbh');
        $this->assign('hxmdbh',$hxmdbh);
        $ysqbh = $this->request->get('ysqbh');
        $this->assign('ysqbh',$ysqbh);
        $jxsmc = $this->request->get('jxsmc');
        $this->assign('jxsmc',$jxsmc);
        $wdmc = $this->request->get('wdmc');
        $this->assign('wdmc',$wdmc);
        $pp = $this->request->get('pp');
        $this->assign('pp',$pp);
        $sjfykm = $this->request->get('sjfykm');
        $this->assign('sjfykm',$sjfykm);
        $sqr = $this->request->get('sqr');
        $this->assign('sqr',$sqr);

        if($start&&$end){
            $where['t.created_on'] = ['between',[strtotime($start),strtotime($end)]];
        }elseif($start){
            $where['t.created_on'] = ['>',strtotime($start)];
        }elseif($end){
            $where['t.created_on'] = ['<',strtotime($end)];
        }
        if($jxsmc){
            $jxs_ids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['t.jxs_id'] = ['in',$jxs_ids];
        }
        if($wdmc){
            $net_ids = db('cg_net')->where('title','like',"%{$wdmc}%")->column('id');
            $where['t3.net_id'] = ['in',$net_ids];
        }
        if($pp){
            $brand_ids = db('pro_products t')
                ->join('pro_sku t2','t2.id=t.sku_id')
                ->join('pro_series t3','t3.id=t2.series_id')
                ->join('pro_category t4','t4.id=t3.category_id')
                ->join('pro_brands t5','t5.id=t4.brand_id')
                ->where('brand_name','like',"%{$pp}%")
                ->column('t.id');
            $where['pro_id'] = ['in',$brand_ids];
        }
        if($sjfykm){
            $feetypeids = db('cg_feiyongkemu3')->where('title','like',"%{$sjfykm}%")->column('id');
            $where['t3.feiyong_id'] = ['in',$feetypeids];
        }
        if($sqr){
            $sqr_ids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['t.c_user_id'] = ['in',$sqr_ids];
        }
        if($hxmdbh){
            $where['t.no'] = ['like',"%{$hxmdbh}%"];
        }
        if($ysqbh){
            $where['t4.no'] = ['like',"%{$ysqbh}%"];
        }

        $count = Db::table('ti_hexiao_apply t')
            ->join('ti_hexiao_detail t2','t2.hexiao_id=t.id')
            ->join('ti_apply_detail t3','t3.id=t2.detail_id')
            ->join('ti_apply t4','t3.apply_id=t4.id')
            ->where($where)->count();
        $data = Db::table('ti_hexiao_apply t')
            ->join('ti_hexiao_detail t2','t2.hexiao_id=t.id')
            ->join('ti_apply_detail t3','t3.id=t2.detail_id')
            ->join('ti_apply t4','t3.apply_id=t4.id')
            ->order("t.id desc")
            ->where($where)
            ->field('t.c_user_id sqr,t.no hxmdbh,t.id,t4.no ydbh,t3.detail_no ymxbh,t3.id ymxid,t.jxs_id,t3.net_id,t3.pro_id product,t3.feiyong_id feetype,t3.fankuijine,t3.hx_yihexiao2,t3.jrbm,t.created_on')
            ->paginate(10,$count);
//        echo Db::table('cg_ti')->getLastSql();
        $page = $data->render();
        $data = $data->items();
        $heji_yhx=$heji_whx=0;
        foreach($data as $k=>$v){
            $v['jxs_title'] = db('cg_jxs')->where('id',$v['jxs_id'])->value('title');
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_title'] = db('cg_system t')->join('cg_net t2','t2.system_id=t.id')->where('t2.id',$v['net_id'])->value('t.title');

            $v['brand'] = db('pro_brands t')
                ->join('pro_category t2','t2.brand_id=t.id')
                ->join('pro_series t3','t3.category_id=t2.id')
                ->join('pro_sku t4','t4.series_id=t3.id')
                ->join('pro_products t5','t5.id=t4.id')
                ->where('t5.id',$v['product'])->value('t.brand_name');

            $v['sjfykm'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            $v['whxje'] = $v['fankuijine']-$v['hx_yihexiao2'];
            $heji_yhx += $v['hx_yihexiao2'];
            $heji_whx += $v['whxje'];
            $v['jrbm'] = db('department')->where('id',$v['jrbm'])->value('de_name');
            $v['cuser'] = db('employee')->where('id',$v['sqr'])->value('em_name');
            $v['ctime'] = date('Y-m-d',$v['created_on']);
            //查询操作记录
            $czjl = db('ti_apply_detail_log')->where('list_id',$v['ymxid'])->order('ctime desc')->select();
            $v['czjl'] = isset($czjl[0]['brief'])?$czjl[0]['brief']:'';
            foreach($czjl as $k1=>$v1){
                $v1['cuser'] = db('employee')->where('id',$v1['cuser'])->value('em_name');
                $v1['ctime'] = date('Y-m-d H:i',$v1['ctime']);
                $czjl[$k1] = $v1;
            }
            $v['czjlxq'] = $czjl;
            $data[$k] = $v;
        }

        $this->assign('heji_yhx',$heji_yhx);
        $this->assign('heji_whx',$heji_whx);
        $this->assign('data',$data);
        $this->assign('page',$page);
        //查询品牌
        $brands = db('pro_brands')->field('id,brand_name')->select();
        $this->assign('brands',$brands);
        $fykm3 = db('cg_feiyongkemu3')->field('id,title')->select();
        $this->assign('fykm3',$fykm3);
        return $this->fetch();
    }

    //导出excel--常规
    public function cgexcel(){
        $where = [];
        $where['t3.buyu_he'] = 1;
        $start = $this->request->get('start');
        $end = $this->request->get('end');
        $hxmdbh = $this->request->get('hxmdbh');
        $ysqbh = $this->request->get('ysqbh');
        $jxsmc = $this->request->get('jxsmc');
        $wdmc = $this->request->get('wdmc');
        $pp = $this->request->get('pp');
        $sjfykm = $this->request->get('sjfykm');
        $sqr = $this->request->get('sqr');

        if($start&&$end){
            $where['t.created_on'] = ['between',[strtotime($start),strtotime($end)]];
        }elseif($start){
            $where['t.created_on'] = ['>',strtotime($start)];
        }elseif($end){
            $where['t.created_on'] = ['<',strtotime($end)];
        }
        if($jxsmc){
            $jxs_ids = db('cg_jxs')->where('title','like',"%{$jxsmc}%")->column('id');
            $where['t.jxs_id'] = ['in',$jxs_ids];
        }
        if($wdmc){
            $net_ids = db('cg_net')->where('title','like',"%{$wdmc}%")->column('id');
            $where['t3.net_id'] = ['in',$net_ids];
        }
        if($pp){
            $brand_ids = db('pro_products t')
                ->join('pro_sku t2','t2.id=t.sku_id')
                ->join('pro_series t3','t3.id=t2.series_id')
                ->join('pro_category t4','t4.id=t3.category_id')
                ->join('pro_brands t5','t5.id=t4.brand_id')
                ->where('brand_name','like',"%{$pp}%")
                ->column('t.id');
            $where['pro_id'] = ['in',$brand_ids];
        }
        if($sjfykm){
            $feetypeids = db('cg_feiyongkemu3')->where('title','like',"%{$sjfykm}%")->column('id');
            $where['t3.feiyong_id'] = ['in',$feetypeids];
        }
        if($sqr){
            $sqr_ids = db('employee')->where('em_name','like',"%{$sqr}%")->column('id');
            $where['t.c_user_id'] = ['in',$sqr_ids];
        }
        if($hxmdbh){
            $where['t.no'] = ['like',"%{$hxmdbh}%"];
        }
        if($ysqbh){
            $where['t4.no'] = ['like',"%{$ysqbh}%"];
        }

        $heji_yhx=$heji_whx=0;
        $data = Db::table('ti_hexiao_apply t')
            ->join('ti_hexiao_detail t2','t2.hexiao_id=t.id')
            ->join('ti_apply_detail t3','t3.id=t2.detail_id')
            ->join('ti_apply t4','t3.apply_id=t4.id')
            ->order("t.id desc")
            ->where($where)
            ->field('t.c_user_id sqr,t.no hxmdbh,t.id,t4.no ydbh,t3.detail_no ymxbh,t3.id ymxid,t.jxs_id,t3.net_id,t3.pro_id product,t3.feiyong_id feetype,t3.fankuijine,t3.hx_yihexiao2,t3.jrbm,t.created_on')
            ->select();
        foreach($data as $k=>$v){
            $v['jxs_title'] = db('cg_jxs')->where('id',$v['jxs_id'])->value('title');
            $v['net_title'] = db('cg_net')->where('id',$v['net_id'])->value('title');
            $v['system_title'] = db('cg_system t')->join('cg_net t2','t2.system_id=t.id')->where('t2.id',$v['net_id'])->value('t.title');

            $v['brand'] = db('pro_brands t')
                ->join('pro_category t2','t2.brand_id=t.id')
                ->join('pro_series t3','t3.category_id=t2.id')
                ->join('pro_sku t4','t4.series_id=t3.id')
                ->join('pro_products t5','t5.id=t4.id')
                ->where('t5.id',$v['product'])->value('t.brand_name');

            $v['sjfykm'] = db('cg_feiyongkemu3')->where('id',$v['feetype'])->value('title');
            $v['whxje'] = $v['fankuijine']-$v['hx_yihexiao2'];
            $heji_yhx += $v['hx_yihexiao2'];
            $heji_whx += $v['whxje'];
            $v['jrbm'] = db('department')->where('id',$v['jrbm'])->value('de_name');
            $v['cuser'] = db('employee')->where('id',$v['sqr'])->value('em_name');
            $v['ctime'] = date('Y-m-d',$v['created_on']);
            //查询操作记录
            $czjl = db('ti_apply_detail_log')->where('list_id',$v['ymxid'])->order('ctime desc')->select();
            $v['czjl'] = isset($czjl[0]['brief'])?$czjl[0]['brief']:'';
            foreach($czjl as $k1=>$v1){
                $v1['cuser'] = db('employee')->where('id',$v1['cuser'])->value('em_name');
                $v1['ctime'] = date('Y-m-d H:i',$v1['ctime']);
                $czjl[$k1] = $v1;
            }
            $v['czjlxq'] = $czjl;
            $data[$k] = $v;
        }

        Vendor("PHPExcel");
        Vendor("PHPExcel.Writer.Excel5");
        $objPHPExcel= new PHPExcel();
        $objProps=$objPHPExcel->getProperties();

        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");

        $objPHPExcel->setActiveSheetIndex(0);
        $as =  $objPHPExcel->getActiveSheet();
        $as->setTitle("TI费用不予核销记录-常规");
        $styleArray = array(
            'borders' => array(
                'allborders' => array(
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
                    'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
                    'color' => array('argb' => ''),
                ),
            ),
        );
        $as->mergeCells('A1:P1');
        $as->setCellValue('A1','TI费用不予核销记录-常规');
        //加粗
        $as->getStyle('A1')->getFont()->setBold(true);
        //字体
        $as->getStyle('A1')->getFont()->setSize(14);
        $as->mergeCells('A2:P2');
        $username = db('employee')->where('id',session('employee_id'))->value('em_name');
        $as->setCellValue('A2','注：本表单由软件导出，导出时间：'.date('Y-m-d H:i').'导出人：'.$username);
        $as->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $as->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        //标题
        $as->getStyle('A3:P3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $as->getStyle('A3:P3')->getFill()->getStartColor()->setARGB('0099CCFF');
        $as->setCellValue('A3','序号');
        $as->setCellValue('B3','核销面单编号');
        $as->setCellValue('C3','原明细编号');
        $as->setCellValue('D3','原申请类型');
        $as->setCellValue('E3','经销商名称');
        $as->setCellValue('F3','系统名称');
        $as->setCellValue('G3','网点名称');
        $as->setCellValue('H3','品牌');
        $as->setCellValue('I3','三级费用科目');
        $as->setCellValue('J3','已核销金额');
        $as->setCellValue('K3','未核销金额');
        $as->setCellValue('L3','费用计入部门');
        $as->setCellValue('M3','操作记录');
        $as->setCellValue('N3','申请人');
        $as->setCellValue('O3','核销日期');
        $as->setCellValue('P3','备注');

        foreach($data as $k=>$v){
            $n = 4+$k;
            $as->setCellValue('A'.$n,$k+1);
            $as->setCellValue('B'.$n,''.$v['hxmdbh']);
            $as->setCellValue('C'.$n,$v['ydbh'].'-'.$v['ymxbh']);
            $as->setCellValue('D'.$n,$v['type_title'].'-'.$v['cate_title']);
            $as->setCellValue('E'.$n,$v['jxs_title']);
            $as->setCellValue('F'.$n,$v['system_title']);
            $as->setCellValue('G'.$n,$v['net_title']);
            $as->setCellValue('H'.$n,$v['brand']);
            $as->setCellValue('I'.$n,$v['sjfykm']);
            $as->setCellValue('J'.$n,$v['hx_yihexiao2']);
            $as->setCellValue('K'.$n,$v['whxje']);
            $as->setCellValue('L'.$n,$v['jrbm']);
            $as->setCellValue('M'.$n,$v['czjl']);
            $as->setCellValue('N'.$n,$v['cuser']);
            $as->setCellValue('O'.$n,$v['ctime']);
            $as->setCellValue('P'.$n,$v['beizhu']);
        }
        $as->mergeCells('A'.(count($data)+4).':I'.(count($data)+4));
        $as->setCellValue('A'.(count($data)+4),'合计');
        $as->setCellValue('J'.(count($data)+4),$heji_yhx);
        $as->setCellValue('K'.(count($data)+4),$heji_whx);
        $as->getStyle('A'.(count($data)+4))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        $n = $as->getHighestRow();
        $as->getStyle('A1:P'.$n)->applyFromArray($styleArray);
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
        ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename=TI费用不予核销记录-常规.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');

    }

    //根据id查找状态
    public function status($id){
        $data = [
            1=>'草稿',
            2=>'已打回',
            3=>'区域领导审批',
            4=>'拆分人审批',
            5=>'集团领导审批',
            6=>'抄送',
            7=>'已归档',
            8=>'已作废',
            9=>'已核销',
        ];
        return $data[$id];
    }

    //返回二维数组中的某二维key的合计
    public function array_sum($arr,$key){
        $sum = 0;
        foreach($arr as $k=>$v){
            $sum += $v[$key];
        }
        return $sum;
    }
}
